/**
 * 修改时间：2009-11-27
 * 修改人：朱家进
 * 修改内容：修改厅渠道类型
 * 
 * 修改时间：2009-12-1
 * 修改人：朱家进
 * 修改内容：添加检测服务厅是否可以切换平台：统计该厅在退货或到货或库存当中,是否有数据,如果有不能修改对应的厅
 * 
 */
package com.zjht.gmcc.servlet.sys;

import java.io.PrintWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.zjht.gmcc.bean.BaseServlet;

import com.zjht.gmcc.bean.util.InitParam;
import com.zjht.gmcc.bean.util.MyConstant;
import com.zjht.gmcc.bean.util.MyJDBC;

public class OrgaEditChannelType extends BaseServlet {

	private static final long serialVersionUID = 1L;

	public boolean process(HttpServletRequest request, HttpServletResponse response, HttpSession session, PrintWriter out, MyJDBC myJDBC, String menu)
			throws Exception {

		int flag = 0;
		String act = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String orgaCode = request.getParameter("orgaCode");
		String channelType = request.getParameter("channelType");
		String orgaName = request.getParameter("orgaName");
		String providerProvOrgaCode = null;// 省级供货平台

		// 先检测服务厅是否可以切换平台：统计该厅在退货或到货或库存当中,是否有数据,如果有不能修改对应的厅
		int count = 0;
		boolean error = false;
		String strErr = "";
		String strProductSystem = "";
		String strHallName = "";

		// 退货(status=0表示退货未审批；status=1表示退货未确认)
		String s_sql1 = "SELECT status,count(*) mycount FROM returnbusi "
				+ "WHERE OrgaCode=? AND (POrgaCode = ? OR POrgaCode = ?) AND (Status=0 OR Status=1) GROUP BY status ORDER BY status";

		String s_sql1_2 = "SELECT a.status,count(*) mycount FROM returnbusi a, returnproduct b "
				+ "WHERE a.OrgaCode=? AND a.POrgaCode = ? AND a.return_id = b.return_id "
				+ "AND (a.Status=0 OR a.Status=1) AND b.productid IN (SELECT productid FROM productgroup WHERE productsystem=?) GROUP BY a.status ORDER BY a.status";

		// 到货
		String s_sql2 = "SELECT count(*) FROM ImeiDn a, IMEIDNPRODUCT b "
				+ "WHERE a.DownCode = b.DownCode AND b.inOrgaCode = ? AND (a.OutOrgaCode = ? OR a.OutOrgaCode = ?) and b.status = 0 ";

		String s_sql2_2 = "SELECT count(*) FROM ImeiDn a, IMEIDNPRODUCT b "
				+ "WHERE a.DownCode = b.DownCode AND b.inOrgaCode = ? AND a.OutOrgaCode = ? and b.status = 0  AND b.productid IN (SELECT productid FROM productgroup WHERE productsystem=?)";

		// 库存
		String s_sql3 = "SELECT count(*) FROM IMEI WHERE OrgaCode = ? AND (CityOrgaCode = ? OR CityOrgaCode = ?)";
		String s_sql3_2 = "SELECT count(*) FROM IMEI WHERE OrgaCode = ? AND CityOrgaCode = ? AND productid IN (SELECT productid FROM productgroup WHERE productsystem=?)";

		PreparedStatement ps1 = myJDBC.prepareStatement(s_sql1);
		PreparedStatement ps1_2 = myJDBC.prepareStatement(s_sql1_2);
		PreparedStatement ps2 = myJDBC.prepareStatement(s_sql2);
		PreparedStatement ps2_2 = myJDBC.prepareStatement(s_sql2_2);
		PreparedStatement ps3 = myJDBC.prepareStatement(s_sql3);
		PreparedStatement ps3_2 = myJDBC.prepareStatement(s_sql3_2);

		String s_sql4 = "SELECT cityproviderorgacode FROM mobiletoprovider WHERE mobileorgacode = ? AND cityprovidertype = 1";
		PreparedStatement ps4 = myJDBC.prepareStatement(s_sql4);
		String s_sql5 = "SELECT cityproviderorgacode FROM mobiletoprovider WHERE mobileorgacode = ? AND cityprovidertype = 2";
		PreparedStatement ps5 = myJDBC.prepareStatement(s_sql5);
		String s_sql6 = "SELECT cityproviderorgacode FROM mobiletoprovider WHERE mobileorgacode = ? AND cityprovidertype = 3";
		PreparedStatement ps6 = myJDBC.prepareStatement(s_sql6);

//		ArrayList arrCityProviderNEW = new ArrayList();
//		ArrayList arrCityProviderTDNEW = new ArrayList();
		ArrayList arrHallCode = new ArrayList();
		ArrayList arrHallName = new ArrayList();
		String cityprovider_new = null;
		String cityprovider_old = "";// 旧市平台2G
		String hall = null;
		String cityproviderTD_new = null;
		String cityproviderTD_old = "";// 旧市平台TD
		String s_sql_HallName = "SELECT ShortName FROM ORGANIZA WHERE OrgaCode= ? ";
		PreparedStatement ps_HallName = myJDBC.prepareStatement(s_sql_HallName);

		ResultSet rs_HallName = null;

		cityprovider_new = request.getParameter("provider");// 新市平台2G
		cityproviderTD_new = request.getParameter("providerTD");// 新市平台TD

		hall = orgaCode;

		ps4.setString(1, orgaCode);
		rs = ps4.executeQuery();
		if (rs.next()) {
			cityprovider_old = rs.getString("cityproviderorgacode");
			cityproviderTD_old = cityprovider_old;
		} else {
			ps5.setString(1, orgaCode);
			rs = ps5.executeQuery();
			if (rs.next())
				cityprovider_old = rs.getString("cityproviderorgacode");

			ps6.setString(1, orgaCode);
			rs = ps6.executeQuery();
			if (rs.next())
				cityproviderTD_old = rs.getString("cityproviderorgacode");
		}

		strHallName = "";
		strProductSystem = "";

		ps_HallName.setString(1, hall);
		rs_HallName = ps_HallName.executeQuery();
		if (rs_HallName.next()) {
			strHallName = rs_HallName.getString(1);
		}
		rs_HallName.close();
		rs_HallName = null;

//		arrCityProviderNEW.add(cityprovider_new);
//		arrCityProviderTDNEW.add(cityproviderTD_new);
		arrHallCode.add(hall);
		arrHallName.add(strHallName);

		// 检查退货(status=0表示退货未审批；status=1表示退货未确认)
		int status = -1;
		strErr = "";

		if (cityprovider_new!=null && cityproviderTD_new !=null && (!cityprovider_new.equals(cityprovider_old)) && cityproviderTD_new.equals(cityproviderTD_old)) {// 只变更了2G手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_2G;
			ps1_2.setString(1, hall);
			ps1_2.setString(2, cityprovider_old);
			ps1_2.setString(3, strProductSystem);
			rs = ps1_2.executeQuery();

		} else if (cityproviderTD_new !=null && cityprovider_new !=null && 
				(!cityproviderTD_new.equals(cityproviderTD_old)) && cityprovider_new.equals(cityprovider_old)) {// 只变更了TD手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_3G;
			ps1_2.setString(1, hall);
			ps1_2.setString(2, cityproviderTD_old);
			ps1_2.setString(3, strProductSystem);
			rs = ps1_2.executeQuery();

		} else {// 2G手机供货商和TD手机供货商都变更
			ps1.setString(1, hall);
			ps1.setString(2, cityprovider_old);
			ps1.setString(3, cityproviderTD_old);
			rs = ps1.executeQuery();
		}

		while (rs.next()) {
			error = true;
			status = rs.getInt(1);
			strErr += (strErr.length() > 0 ? "，" : "") + (status == 0 ? "区分公司未审批：" : "市平台未确认：") + rs.getInt(2) + "个";
		}
		rs.close();
		rs = null;

		if (error) {
			alert(out, act + "失败\\n\\n原因：<" + arrHallName.get(0) + ">的数据提交失败");
			return false;
		}

		// 检查到货
		count = 0;
		strErr = "";
		strProductSystem = "";
		if (cityprovider_new!=null && cityproviderTD_new !=null && (!cityprovider_new.equals(cityprovider_old)) && cityproviderTD_new.equals(cityproviderTD_old)) {// 只变更了2G手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_2G;
			ps2_2.setString(1, hall);
			ps2_2.setString(2, cityprovider_old);
			ps2_2.setString(3, strProductSystem);
			rs = ps2_2.executeQuery();
		} else if (cityproviderTD_new !=null && cityprovider_new !=null 
				&& (!cityproviderTD_new.equals(cityproviderTD_old)) && cityprovider_new.equals(cityprovider_old)) {// 只变更了TD手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_3G;
			ps2_2.setString(1, hall);
			ps2_2.setString(2, cityproviderTD_old);
			ps2_2.setString(3, strProductSystem);
			rs = ps2_2.executeQuery();
		} else {// 2G手机供货商和TD手机供货商都变更
			ps2.setString(1, hall);
			ps2.setString(2, cityprovider_old);
			ps2.setString(3, cityproviderTD_old);
			rs = ps2.executeQuery();
		}

		while (rs.next()) {
			count = rs.getInt(1);
		}
		rs.close();
		rs = null;

		if (count > 0) {
			error = true;
			alert(out, act + "失败\\n\\n原因：<" + arrHallName.get(0) + ">的数据提交失败");
			return false;
		}

		// 检查库存
		count = 0;
		strErr = "";
		strProductSystem = "";
		if (cityprovider_new!=null && cityproviderTD_new !=null && (!cityprovider_new.equals(cityprovider_old)) && cityproviderTD_new.equals(cityproviderTD_old)) {// 只变更了2G手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_2G;
			ps3_2.setString(1, hall);
			ps3_2.setString(2, cityprovider_old);
			ps3_2.setString(3, strProductSystem);
			rs = ps3_2.executeQuery();
		} else if (cityproviderTD_new !=null && cityprovider_new !=null 
				&& (!cityproviderTD_new.equals(cityproviderTD_old)) && cityprovider_new.equals(cityprovider_old)) {// 只变更了TD手机供货商
			strProductSystem = MyConstant.PRODUCTSYSTEM_3G;
			ps3_2.setString(1, hall);
			ps3_2.setString(2, cityproviderTD_old);
			ps3_2.setString(3, strProductSystem);
			rs = ps3_2.executeQuery();
		} else {// 2G手机供货商和TD手机供货商都变更
			ps3.setString(1, hall);
			ps3.setString(2, cityprovider_old);
			ps3.setString(3, cityproviderTD_old);
			rs = ps3.executeQuery();
		}

		while (rs.next()) {
			count = rs.getInt(1);
		}
		rs.close();
		rs = null;

		if (count > 0) {
			error = true;
				}
		if (error) {
			alert(out, act + "失败\\n\\n原因：<" + arrHallName.get(0) + ">的数据提交失败");
			return false;
		}

		if (!error) {
			String s_sql = "UPDATE organiza SET channelType = ? WHERE orgacode = ?";
			ps = myJDBC.prepareStatement(s_sql);
			ps.setString(1, channelType);
			ps.setString(2, orgaCode);

			flag = ps.executeUpdate();
			ps.close();

			if (flag == 1  && !MyConstant.CHANNEL_FD.equals(channelType)) {
				String provider = request.getParameter("provider");
				String providerTD = (request.getParameter("providerTD") == null) ? "" : request.getParameter("providerTD");

				s_sql = "SELECT parentorgacode FROM organiza WHERE orgacode = ? AND channeltype = ?  ";
				ps = myJDBC.prepareStatement(s_sql);
				ps.setString(1, provider);
				ps.setString(2, channelType);
				rs = ps.executeQuery();
				if (rs.next()) {
					providerProvOrgaCode = rs.getString("parentorgacode");
				}

				s_sql = "INSERT INTO MOBILETOPROVIDEROLD "
						+ "SELECT mobileorgacode,providerorgacode,cityproviderorgacode,validdate,sysdate,cityprovidertype,multiplat "
						+ "FROM MOBILETOPROVIDER  WHERE mobileorgacode = ?";
				ps = myJDBC.prepareStatement(s_sql);
				ps.setString(1, orgaCode);
				ps.executeUpdate();

				// 删除所有对映关系
				s_sql = "DELETE MOBILETOPROVIDER WHERE mobileorgacode = ?  AND multiplat = '0'  ";
				ps = myJDBC.prepareStatement(s_sql);
				ps.setString(1, orgaCode);
				ps.executeUpdate();

				if (!channelType.equals("01")) {
					s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
							+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '1')";
					ps = myJDBC.prepareStatement(s_sql);
					ps.setString(1, orgaCode);
					ps.setString(2, providerProvOrgaCode);
					ps.setString(3, provider);
					flag = ps.executeUpdate();
					ps.close();
				} else {
					if ("0".equals(providerTD)) {// 没有选择TD手机供货商

						s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
								+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '2')";
						ps = myJDBC.prepareStatement(s_sql);
						ps.setString(1, orgaCode);
						ps.setString(2, providerProvOrgaCode);
						ps.setString(3, provider);
						flag = ps.executeUpdate();
						ps.close();

					} else {// 选择TD手机供货商
						if (providerTD != null && providerTD.equals(provider)) {// 2G手机和TD手机是同一个供货商

							s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
									+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '1')";
							ps = myJDBC.prepareStatement(s_sql);
							ps.setString(1, orgaCode);
							ps.setString(2, providerProvOrgaCode);
							ps.setString(3, provider);
							flag = ps.executeUpdate();
							ps.close();
							ps = null;
						} else {// 2G手机和TD手机不是同一个供货商
							// 插入2G手机供货商
							if (provider != null) {

								s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
										+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '2')";
								ps = myJDBC.prepareStatement(s_sql);
								ps.setString(1, orgaCode);
								ps.setString(2, providerProvOrgaCode);
								ps.setString(3, provider);
								flag = ps.executeUpdate();
								ps.close();
								ps = null;
							}
							// 插入TD手机供货商
							if (flag == 1 && providerTD != null) {
								s_sql = "SELECT parentorgacode FROM organiza WHERE orgacode = ? AND channeltype = ?  ";
								ps = myJDBC.prepareStatement(s_sql);
								ps.setString(1, providerTD);
								ps.setString(2, channelType);
								rs = ps.executeQuery();
								if (rs.next()) {
									providerProvOrgaCode = rs.getString("parentorgacode");
								}

								s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
										+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '3')";
								ps = myJDBC.prepareStatement(s_sql);
								ps.setString(1, orgaCode);
								ps.setString(2, providerProvOrgaCode);
								ps.setString(3, providerTD);
								flag = ps.executeUpdate();
								ps.close();
								ps = null;
							}
						}
					}
				}

			}else{//修改为FD渠道类型
				String[] providers = request.getParameterValues("platFD");
				s_sql = "SELECT parentorgacode FROM organiza WHERE orgacode = ? AND channeltype = ?  ";
				PreparedStatement ps_sel = ps = myJDBC.prepareStatement(s_sql);
				s_sql = "insert into MOBILETOPROVIDER(mobileorgacode, providerorgacode, cityproviderorgacode, status, "
					+ "ValidDate, CityProviderType) " + "VALUES(?, ?, ?, 1, Sysdate, '1')";
				PreparedStatement ps_in = myJDBC.prepareStatement(s_sql);
				if(providers != null && providers.length > 0){
					s_sql = "DELETE MOBILETOPROVIDER WHERE mobileorgacode = ? ";
					ps = myJDBC.prepareStatement(s_sql);
					ps.setString(1, orgaCode);
					ps.executeUpdate();
					ps.close();
					
					for(String provider : providers){
						ps_sel.setString(1, provider);
						ps_sel.setString(2, channelType);
						rs = ps_sel.executeQuery();
						if (rs.next()) {
							providerProvOrgaCode = rs.getString("parentorgacode");
						}
						rs.close();
						
						ps_in.setString(1, orgaCode);
						ps_in.setString(2, providerProvOrgaCode);
						ps_in.setString(3, provider);
						flag = ps_in.executeUpdate();
						if(flag == 0)
							break;
					}
				}
				ps_sel.close();
				ps_in.close();
			}
			act = orgaName + " 渠道类型修改";
		}

		if (flag > 0) {
			InitParam.initOrgaInfo();
			// alert(out,act+ "成功", "/sys/orga/orga.jsp?orgacode="+orgaCode+"&orgaType=4");
			alertOK(out, act + "成功！", "dialogArguments.location.href='orga/orga.jsp?orgacode=" + orgaCode + "&channelType=" + channelType
					+ "&orgaType=4';window.returnValue='" + orgaCode + "';window.close();");
		} else {
			errAlert(request, out, menu, act + "失败");
		}
		return flag > 0;
	}
}
